﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapQVW_VAFARBEN(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveQVW_VAFARBEN(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryQVW_VAFARBEN(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapQVW_VAFARBEN(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertQVW_VAFARBEN(dwData, 1);
            }
            UpdateQVW_VAFARBEN(dwLocal);

        }
        public void InsertQVW_VAFARBEN(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://20140501
                    or1.CommandText = "INSERT INTO  QVW_VAFARBEN  "
                        + "SELECT DISTINCT  VAFKOLL,VAFART,VAFFARBE,VAFPGROE,MAX(VAAARTST) AS ARTST,VAFKOLL||VAFART AS VART,VAFKOLL||VAFART||VAFFARBE AS VFARBE,VAFKOLL||VAFART||VAFFARBE||VAFPGROE AS VASKU,"
                        + "VAFBEZ AS VFBEZ,VAFPART||VAFPVAR AS PFART,ROUND( MAX(VKPPREIS*0.001*VKKKURS*0.0001/(VKPMEEHFKT*0.000001))* dbms_random.value(0.7,1.5),2) AS PFCOST,VAFPART  AS PART,VAFPVAR  AS PVAR,VAFFFARBE AS PCOLOR,VAFDESS AS PDESS,VAFCOLORIT AS PDCWY, VAFBESCHICH AS PFINISH,VAFPGROE AS PSIZE,0.0 AS STDCOST,0.0 AS EKCOST,0.0 AS PLANCOST"
                        + " FROM VAFARBEN F JOIN VKPOS P ON  VAFKOLL=VKPKOLL AND   VKPART=VAFART AND VKPFARBE=VAFFARBE JOIN VKKOPF ON VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD=VKPAUFLFD AND VKKMANDANT=VKPMANDANT"
                        + " JOIN VAART ON VAAKOLL=VAFKOLL AND VAAART=VAFART WHERE VAFMANDANT=:MANDANT AND VKKSA='  ' AND VKPSA='  ' AND VKPMEEHFKT<>'000000000000'"
                        + " GROUP BY VAFMANDANT,VAFKOLL,VAFART,VAFFARBE,VAFPGROE,VAFBEZ,VAFPART,VAFPVAR,VAFDESS,VAFCOLORIT,VAFBESCHICH,VAFFFARBE";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DeleteQVW_VAFARBEN(DataRow dwData, int option = 1)
        {
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QVW_VAFARBEN X";
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryQVW_VAFARBEN(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM QVW_VAFARBEN ";
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "QVW_VAFARBEN";
            Return = Result;
            return Return;
        }
        public void UpdateQVW_VAFARBEN(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "UPDATE QVW_VAFARBEN F SET (PFCOST,STDCOST,EKCOST,PLANCOST)=(SELECT  MAX(STDCOST),MAX(STDCOST),MAX(EKCOST),MAX(PLANCOST)"
                        + "  FROM QVW_FWASTAMM KS WHERE   RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND F.PART=KS.PART) WHERE EXISTS (SELECT 1 FROM QVW_FWASTAMM KS WHERE F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART))"; ;
                    break;
                case 2:
                    or1.CommandText = "UPDATE QVW_VAFARBEN F SET  (PFCOST,STDCOST,EKCOST,PLANCOST)=(SELECT  MAX(STDCOST),MAX(STDCOST),MAX(EKCOST),MAX(PLANCOST)"
                        + "  FROM QVW_GWASTAMM KS WHERE   RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND F.PART=KS.PART) WHERE EXISTS (SELECT 1 FROM QVW_GWASTAMM KS WHERE  F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART))"; ;
                    break;
                case 3:
                    or1.CommandText = "UPDATE QVW_VAFARBEN F SET (PFCOST,STDCOST,EKCOST,PLANCOST)=(SELECT  MAX(STDCOST),MAX(STDCOST),MAX(EKCOST),MAX(PLANCOST)"
                        + "  FROM QVW_FSSTAMM KS WHERE  RTRIM(F.ARTST)=RTRIM(KS.ARTST) AND F.PART=KS.PART) WHERE EXISTS (SELECT 1 FROM QVW_FSSTAMM KS WHERE F.ARTST=KS.ARTST AND RTRIM(F.PART)=RTRIM(KS.PART))"; ;
                    break; 
            }
            Connect2Proxy(or1);
        }
    }
}
